package com.admin.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.MobileUserBean;


@Repository
public class MobileUserDao {
	
	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;
	
	
	/**
	 * 注册用户
	 * @param user
	 * @return
	 */
	public int addUser(MobileUserBean user){
		String sql = "insert into mobile_user(sid,cid,smsp,ismi,softwareVersion,userAgent,cardType,btAddr,imei,lcdHeight,lcdWidth,telPhone," +
				"isp,provinceId,userType,midc,regCount)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = new Object[]{user.getSid(),user.getCid(),user.getSmsp(),user.getIsmi(),user.getSoftwareVersion(),user.getUserAgent(),
				user.getCardType(),user.getBtAddr(),user.getImei(),user.getLcdHeight(),user.getLcdWidth(),user.getTelPhone(),user.getIsp(),
				user.getProvinceId(),user.getUserType(),user.getMidc(),user.getRegCount()};
		return jdbcTemplate.update(sql, params);
	}
	
	/**
	  * @Title: updateUser
	  * @Description:更新用户信息 
	  * @param @param user
	  * @param @return
	  * @return int
	  * @throws
	 */
	public int updateUser(MobileUserBean user){
		String sql = "update mobile_user set sid=?,cid=?,smsp=?,ismi=?,softwareVersion=?,userAgent=?,cardType=?,btAddr=?,imei=?,lcdHeight=?," +
				"lcdWidth=?,isp=?,provinceId=?,userType=?,midc=?,regCount=?,telPhone=? where userId=?";
		Object[] params = new Object[]{user.getSid(),user.getCid(),user.getSmsp(),user.getIsmi(),user.getSoftwareVersion(),user.getUserAgent(),
				user.getCardType(),user.getBtAddr(),user.getImei(),user.getLcdHeight(),user.getLcdWidth(),user.getIsp(),user.getProvinceId(),
				user.getUserType(),user.getMidc(),user.getRegCount(),user.getTelPhone(),user.getUserId()};
		return jdbcTemplate.update(sql, params);
	}
	
	/**
	 * 修改手机号码
	 * @param smsp
	 * @param ismi
	 * @param telPhone
	 * @return
	 */
	public int updateTelPhone(String smsp,String ismi,String telPhone,int provinceId){
		String sql = "update mobile_user set telPhone = ?,provinceId=? where smsp = ? and ismi = ?";
		Object[] params = new Object[]{telPhone,provinceId,smsp,ismi};
		return jdbcTemplate.update(sql, params);
	}
	
	/**
	 * 修改用户类型
	 * @param smsp
	 * @param ismi
	 * @param telPhone
	 * @return
	 */
	public int updateUserType(int userType,int userId){
		String sql = "update mobile_user set userType = ? where userId = ?";
		Object[] params = new Object[]{userType,userId};
		return jdbcTemplate.update(sql, params);
	}
	
	
	/**
	 * 根据用户ID查询用户信息
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public MobileUserBean findByUserId(int userId) throws SQLException{
		String sql = "select u.*,c.companyName,m.mobileTypeName from mobile_user u,company c,mobile_type m where u.userId = ? and u.sid=c.companyId and u.cid=m.mobileTypeId";
		return queryRunner.query(sql, new BeanHandler<MobileUserBean>(MobileUserBean.class), userId);
	}
	
	/**
	 * 根据SMSP和ISMI查询用户是否存在
	 * @param smsp
	 * @param ismi
	 * @return
	 */
	public int findCountBySmspAndIsmi(String smsp,String ismi){
		String sql = "select count(0) from mobile_user where smsp = ? and ismi = ?";
		return jdbcTemplate.queryForInt(sql,new Object[]{smsp,ismi});
	}
	
	/**
	 * 根据条件查询用户数
	 * @param user
	 * @return
	 */
	public int findCountByCondition(MobileUserBean user){
		StringBuffer sql = new StringBuffer("select count(0) from mobile_user where 1=1");
		List<Object> params = new ArrayList<Object>();
		if(user.getIsp()>=0){
			sql.append(" and isp=?");
			params.add(user.getIsp());
		}
		if(user.getProvinceId()>=0){
			sql.append(" and provinceId=?");
			params.add(user.getProvinceId());
		}
		if(user.getSid()>0){
			sql.append(" and sid=?");
			params.add(user.getSid());
		}
		if(user.getUserType()>=0){
			sql.append(" and userType=?");
			params.add(user.getUserType());
		}
		if(StringUtils.isNotEmpty(user.getTelPhone())){
			sql.append(" and telPhone=?");
			params.add(user.getTelPhone());
		}
		return jdbcTemplate.queryForInt(sql.toString(),params.toArray());
	}
	
	/**
	 * 根据条件查询用户
	 * @param user
	 * @param start
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public List<MobileUserBean> findByCondition(MobileUserBean user,int start,int pageSize) throws SQLException{
		StringBuffer sql = new StringBuffer("select u.*,c.companyName,m.mobileTypeName from mobile_user u,company c,mobile_type m where 1=1");
		List<Object> params = new ArrayList<Object>();
		if(user.getIsp()>=0){
			sql.append(" and u.isp=?");
			params.add(user.getIsp());
		}
		if(user.getProvinceId()>=0){
			sql.append(" and u.provinceId=?");
			params.add(user.getProvinceId());
		}
		if(user.getSid()>0){
			sql.append(" and u.sid=?");
			params.add(user.getSid());
		}
		if(user.getUserType()>=0){
			sql.append(" and u.userType=?");
			params.add(user.getUserType());
		}
		if(StringUtils.isNotEmpty(user.getTelPhone())){
			sql.append(" and u.telPhone=?");
			params.add(user.getTelPhone());
		}
		sql.append(" and u.sid=c.companyId and u.cid=m.mobileTypeId order by u.createTime desc limit ?,?");
		params.add(start);
		params.add(pageSize);
		return queryRunner.query(sql.toString(), new BeanListHandler<MobileUserBean>(MobileUserBean.class), params.toArray());
	}
	
	/**
	 * 根据手机号码查询用户
	 * @param telPhone
	 * @return
	 * @throws SQLException
	 */
	public MobileUserBean findByTelPhone(String telPhone) throws SQLException{
		String sql = "select * from mobile_user where telPhone = ?";
		return queryRunner.query(sql, new BeanHandler<MobileUserBean>(MobileUserBean.class), telPhone);
	}
	
	/**
	 * 根据smsp和ismi查询用户
	 * @param smsp
	 * @param ismi
	 * @return
	 * @throws SQLException
	 */
	public MobileUserBean findBySmspAndIsmi(String smsp, String ismi){
		String sql = "select * from mobile_user where smsp = ? and ismi = ?";
		try {
			return queryRunner.query(sql, new BeanHandler<MobileUserBean>(MobileUserBean.class), new Object[]{smsp,ismi});
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 
	  * @Title: delUser
	  * @Description:删除用户 
	  * @param @param userId
	  * @param @return
	  * @return int
	  * @throws
	 */
	public int delUser(int userId){
		String sql = "delete from mobile_user where userId = ?";
		return jdbcTemplate.update(sql, userId);
	}
}
